package com.v1.video.option.db;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.v1.video.domain.Header;
import com.v1.video.domain.LoginInfo;
import com.v1.video.domain.PlayerInfo;
import com.v1.video.util.Logger;

public class PersonalCenterUploadDB {
    private static final String TAG = "PersonalCenterUploadDB";
	private static PersonalCenterUploadDB myDBInstance = null;
	
	private SQLiteDatabase mDatabase = null;
	private String mDBFilename = "ppload";//personupload的简称
	
	private PersonalCenterUploadDB(Context context)
	{
	    db_open(context);
	}
	
	public static PersonalCenterUploadDB getInstance(Context context)
	{
		if( myDBInstance == null )
		{
			myDBInstance = new PersonalCenterUploadDB(context);
		}
		
		return myDBInstance;
	}
	
	public int db_open(Context context)
	{
		if (context == null) {
			return 1;
		}
	    String userid = "";//
	    if(LoginInfo.getInstance().getUserIdOld() != null && LoginInfo.getInstance().getUserIdOld().length() > 0){
	    	userid = LoginInfo.getInstance().getUserIdOld();
	    }else{
	    	userid = LoginInfo.getInstance().getUserId();
	    }
	    Logger.i(TAG, "userid=="+userid);
	    Logger.i(TAG, "mDBFilename+userid+.db="+mDBFilename+userid+".db");
		mDatabase = context.openOrCreateDatabase(mDBFilename+userid+".db", Context.MODE_PRIVATE, null);
		if( mDatabase == null )
			return 1;

		create();
		
		return 0;
	}
	
	public void db_close()
	{
		if(mDatabase != null)
		{
			mDatabase.close();
		}
		mDatabase = null;
		myDBInstance = null;
	}	
	
	public void create()
	{		
		String sql = "create table if not exists personUpload("
				+ "sourceid varchar(256) primary key," //视频唯一标志 v1_视频
				+ "user_Id varchar(256)," //用户id
				+ "keyType varchar(256)," //操作指令
				+ "related_Video_Id varchar(256)," //相关视频id个人中心里为""，二期用来存放：来源：是圈子？活动？还是个人中心
				+ "title varchar(256)," //标题
				+ "detail varchar(256)," //详情
				+ "address varchar(256)," //视频上传地址
				+ "scenario varchar(256)," //视频场景		二期用来存放：圈子id，或者活动id了；
				+ "duration varchar(256)," //播放时间长度 秒
				+ "Content_Length varchar(256)," //视频上传的字节长度
				+ "filename varchar(256)," //视频原始名称
				+ "ssotype varchar(256)," //
				+ "comments varchar(256)," //二期用来存放虚拟地理位置 
				+ "state varchar(256)," //
				+ "filePath varchar(256)" //
				+ ");";
		//创 建 的表：用以存储来自新浪的好友注册的V1用户
		String sqlSina = "create table if not exists sinaV1Friends"+LoginInfo.getInstance().getUserId()+"("
				+ "userId varchar(256)," //好友用户id
				+ "userSex varchar(256)," //性别
				+ "userImg varchar(256)," //头像
				+ "userName varchar(256)," //名称
				+ "nickName varchar(256)," //首字线
				+ "sinaName varchar(256)," //新浪微博名称
				+ "sinaId varchar(256)," //
				+ "sinaImg varchar(256)," //
				+ "detail varchar(256)," //简介
				+ "address varchar(256)," //			
				+ "state varchar(256)," //
				+ "type varchar(256)," //
				+ "style varchar(256)" //
				+ ");";
				 
		mDatabase.execSQL(sql);			
		mDatabase.execSQL(sqlSina);	
	}
	
	public boolean execSQL(String sql)
	{
		try
		{
			mDatabase.execSQL(sql);
			return true;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			Logger.v("error", sql);
			return false;
		}
	}
	
	
	public Cursor query(String sql)
	{
		return mDatabase.rawQuery(sql, null);
	}
	
	public static int insertUploadInfo(Header info,Context context){
	    if(info == null){
	        return -1;
	    }
	    
	    String sql = String
                .format("insert into personUpload(sourceid,user_Id,keyType,related_Video_Id,title," +
                		"detail,address,scenario,duration,Content_Length,filename,ssotype,comments,state,filePath) values("
                        + "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
                        info.sourceid,info.user_Id,info.keyType,info.related_Video_Id,info.title,
                        info.detail,info.address,info.scenario,info.duration,info.Content_Length,
                        info.filename,info.ssotype,info.comments,info.state,info.filePath);
	    Logger.i(TAG, "插入sql=="+sql);
	    ArrayList<Header> infoList = getUploadInfos(context);
	    if(infoList != null && infoList.size() > 0){
	        boolean isSame = false;
	        for(int i = 0; i < infoList.size();i++){
	            Header header = infoList.get(i);
	            if(header.sourceid.equals(info.sourceid)){
	                isSame = true;
	                break;
	            }
	        }
	        if(!isSame){
	            if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql)) {
	                return -1;
	            }
	        }else{
	            updataUploadInfo(info, context);
	        }
	    }
	    if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql)) {
            return -1;
        }
	    return 1;
	}
	
	/**
     * 删除对象
     * 
     * @param info
     * @return
     */
    public static boolean deleteUploadInfo(Header info,Context context) {
        String sql = String.format("delete from personUpload where sourceid='%s' ",
                info.sourceid);
        if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql))
            return false;

        return true;
    }
    
    public static boolean updataUploadInfo(Header info,Context context){
        String sql = String
                .format("update personUpload set state = "+info.state
                        + " where sourceid='%s'",                        
                        info.sourceid);
        Logger.i(TAG, "更新sql=="+sql);
        if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql))
            return false;

        return true;
    }
    
    /**
     * 读取定时列表
     * 
     * @return
     */
    public static ArrayList<Header> getUploadInfos(Context context) {
        String sql = "select * from personUpload";
        ArrayList<Header> infoList = new ArrayList<Header>();
        Cursor cursor = null;
        try{
            cursor = PersonalCenterUploadDB.getInstance(context).query(sql);
            if (!cursor.moveToFirst()) {
                cursor.close();
                return null;
            }
            do {
                Header header = new Header();
                header.sourceid = cursor.getString(cursor.getColumnIndex("sourceid"));
                header.user_Id = cursor.getString(cursor.getColumnIndex("user_Id"));
                header.address = cursor.getString(cursor.getColumnIndex("address"));
                header.comments = cursor.getString(cursor.getColumnIndex("comments"));
                header.Content_Length = cursor.getString(cursor.getColumnIndex("Content_Length"));
                header.detail = cursor.getString(cursor.getColumnIndex("detail"));
                header.duration = cursor.getString(cursor.getColumnIndex("duration"));
                header.filename = cursor.getString(cursor.getColumnIndex("filename"));
                header.keyType = cursor.getString(cursor.getColumnIndex("keyType"));
                header.related_Video_Id = cursor.getString(cursor.getColumnIndex("related_Video_Id"));
                header.scenario = cursor.getString(cursor.getColumnIndex("scenario"));
                header.ssotype = cursor.getString(cursor.getColumnIndex("ssotype"));
                header.title = cursor.getString(cursor.getColumnIndex("title"));
                header.state = cursor.getString(cursor.getColumnIndex("state"));
                header.filePath = cursor.getString(cursor.getColumnIndex("filePath"));
                infoList.add(header);
            }while(cursor.moveToNext());
            
            cursor.close();
            return infoList;
        }catch(Exception e){
            e.printStackTrace();
            if(cursor != null){
                cursor.close();
                cursor = null;
            }
        }
        return infoList;
    }
    
    public static int insertV1SinaInfo(PlayerInfo info,Context context){
	    if(info == null){
	        return -1;
	    }

//      + "userId varchar(256)," //好友用户id
//		+ "userSex varchar(256)," //性别
//		+ "userImg varchar(256)," //头像
//		+ "userName varchar(256)," //名称
//		+ "nickName varchar(256)," //首字线
//		+ "sinaName varchar(256)," //新浪微博名称
//		+ "sinaId varchar(256)," //
//		+ "sinaImg varchar(256)," //
//		+ "detail varchar(256)," //简介
//		+ "address varchar(256)," //			
//		+ "state varchar(256)," //
//		+ "type varchar(256)," //
//		+ "style varchar(256)" //
	    String sql = String
                .format("insert into sinaV1Friends"+LoginInfo.getInstance().getUserId()+"(userId,userSex,userImg,userName,sinaName," +
                		"detail,address,state,type,style) values("
                        + "'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
                        info.getUserId(),info.getSex(),info.getUserImg(),info.getUserName(),info.getSinaName(),
                        info.getDetail(),info.getRegion(),info.getState(),"0","0");
	    Logger.i(TAG, "插入sql=="+sql);
	    ArrayList<PlayerInfo> infoList = getV1SinaInfo(context);
	    if(infoList != null && infoList.size() > 0){
	        boolean isSame = false;
	        for(int i = 0; i < infoList.size();i++){
	        	PlayerInfo userInfo = infoList.get(i);
	            if(userInfo.getUserId() == info.getUserId()){
	                isSame = true;
	                break;
	            }
	        }
	        if(!isSame){
	            if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql)) {
	                return -1;
	            }
	        }else{
	            updataV1SinaInfo(info, context);
	        }
	    }
	    if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql)) {
            return -1;
        }
	    return 1;
	}
	
	/**
     * 删除对象
     * 
     * @param info
     * @return
     */
    public static boolean deleteV1SinaInfo(PlayerInfo info,Context context) {
        String sql = String.format("delete from sinaV1Friends"+LoginInfo.getInstance().getUserId()+" where userId='%s' ",
                ""+info.getUserId());
        if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql))
            return false;
        return true;
    }
    
    public static boolean updataV1SinaInfo(PlayerInfo info,Context context){
        String sql = String
                .format("update sinaV1Friends" + LoginInfo.getInstance().getUserId()+" set state = "+info.getState()
                        + " where userId='%s'",               
                        ""+info.getUserId());
        Logger.i(TAG, "更新sql=="+sql);
        if (!PersonalCenterUploadDB.getInstance(context).execSQL(sql))
            return false;

        return true;
    }
    
    /**
     * 读取定时列表
     * 
     * @return
     */
    public static ArrayList<PlayerInfo> getV1SinaInfo(Context context) {
        String sql = "select * from sinaV1Friends"+LoginInfo.getInstance().getUserId();
        ArrayList<PlayerInfo> infoList = new ArrayList<PlayerInfo>();
        Cursor cursor = null;
        try{
            cursor = PersonalCenterUploadDB.getInstance(context).query(sql);
            if (!cursor.moveToFirst()) {
                cursor.close();
                return null;
            }
            do {
            	PlayerInfo userInfo = new PlayerInfo();
            	userInfo.setUserId(Long.parseLong(cursor.getString(cursor.getColumnIndex("userId"))));
            	userInfo.setSex(cursor.getString(cursor.getColumnIndex("userSex")));
            	userInfo.setUserImg(cursor.getString(cursor.getColumnIndex("userImg")));
            	userInfo.setUserName(cursor.getString(cursor.getColumnIndex("userName")));
            	userInfo.setSinaName(cursor.getString(cursor.getColumnIndex("sinaName")));
            	userInfo.setDetail(cursor.getString(cursor.getColumnIndex("detail")));
            	userInfo.setState(cursor.getString(cursor.getColumnIndex("state")));      	
                infoList.add(userInfo);
            }while(cursor.moveToNext());
            
            cursor.close();
            return infoList;
        }catch(Exception e){
            e.printStackTrace();
            if(cursor != null){
                cursor.close();
                cursor = null;
            }
        }
        return infoList;
    }
}
